<?php

    $conf['db_hostname'] = 'localhost';
    $conf['db_username'] = 'SQLPamoka_26s_f4';
    $conf['db_password'] = '12gfg5xg';
    $conf['db_name1'] = 'SQLPamokos';
    $conf['db_name2'] = 'SQLPamokos_temp';
	$conf['db_name3'] = 'SQLPamokos_vartotojai';
    //$conf['address'] = ''; 
/*
    $conf['db_hostname'] = '127.0.0.1:3307';
//    $conf['db_hostname'] = 'localhost';
    $conf['db_username'] = 'root';
    $conf['db_password'] = '';
    $conf['db_name1'] = 'work';
    $conf['db_name2'] = 'work_temp';
    $conf['db_name3'] = 'registeredusers';  
*/
    $workDB = mysql_connect($conf['db_hostname'], $conf['db_username'], $conf['db_password'], true) or die('Error connecting to mysql');
    $tempDB = mysql_connect($conf['db_hostname'], $conf['db_username'], $conf['db_password'], true) or die('Error connecting to mysql');
    $loginDB = mysql_connect($conf['db_hostname'], $conf['db_username'], $conf['db_password'], true) or die('Error connecting to mysql');
    mysql_select_db($conf['db_name1'], $workDB);
    mysql_select_db($conf['db_name2'], $tempDB);
	mysql_select_db($conf['db_name3'], $loginDB);
    
    mysql_set_charset('utf8', $workDB);
    mysql_set_charset('utf8', $tempDB);
	mysql_set_charset('utf8', $loginDB);

    $selectdata_name = 'uzduotis_select';
	$insertdata_name = 'uzduotis_insert';
	$updatedata_name = 'uzduotis_update';
	$deletedata_name = 'uzduotis_delete';
    
    $selectdata = FetchTaskFromDB($selectdata_name, $workDB);
    $insertdata = FetchTaskFromDB($insertdata_name, $workDB);
    $updatedata = FetchTaskFromDB($updatedata_name, $workDB);
	$deletedata = FetchTaskFromDB($deletedata_name, $workDB);
	
//	$tableForTask = FetchindexFromDB('', $workDB);

    // naikina blogus simbolius. mysql_real_escape_string netinka, nes kabutes pakeičia į tokias, kokių MySQL nesupranta. Taip pat išnaikinam su šia
    // funkcija ir komentaro ženklą.
    function escapeString($str) {
        if(!empty($str) && is_string($str)) { 
            return str_replace(array('\\', "\0", "\n", "\r", "#", "\x1a"), array('\\\\', '\\0', '', '', "", '\\Z'), $str); 
        } 
        return $str; 
    }
	
	function FetchTaskFromDB($table, $db)
	{
		// Nuskaito duomenis iš DB. Naudojama nuskaityti užduotims
		
		$query = sprintf("SELECT * FROM %s", $table);
		$result = mysql_query($query, $db);
        $array =  array();
        while ($row = mysql_fetch_array($result)) {
            $array[] = array(
                'id' => $row['id'],
                'apr' => $row['salyga'],
                'com' => $row['komanda'],
                'tbl' => $row['lentele']
            );
		}	
		return $array;
	}



    function FetchCurrentUserData($email)
    {
    	// Aktyvus/neaktyvus
    	// Vardas, pavardė
    	// Rolės
        global  $loginDB;

        $query = sprintf("SELECT IsAdmin, IsActive FROM users WHERE eMail='%s' AND IsActive='1'", $email);
        $result = mysql_query($query, $loginDB);
        if (!$result) {
            return array('IsActive' => false, 'IsAdmin' => false, 'allRoles' => '');
			}
        if (mysql_num_rows($result) < 1) {
            $loginData = array('IsActive' => false, 'IsAdmin' => false);
		}
        else
        {
            $tmp = mysql_fetch_assoc($result);
			$adm = $tmp['IsAdmin'] == "1";
			$loginData = array('IsActive' => true, 'IsAdmin' => $adm);
        }
        mysql_free_result($result);
        if ($loginData['IsActive'] != "1")   // Neprisijungęs vartojas rolių neturi.
        {
            $loginData['allRoles'] = '';
            return $loginData;
        }

        $loginData['allRoles'] = GetPersonRoles($email);
        return $loginData;
    }

    // Nuskaito vieno asmens roles
    function GetPersonRoles($email)
    {
    	// Nuskaito visos roles, priklausančias vienam žmogui
        global  $loginDB;

        $query = sprintf("SELECT RoleID FROM userinrole WHERE eMail='%s'", $email);
        $result = mysql_query($query, $loginDB);
        if (!$result)
        {
            $loginData['allRoles'] = '';
            return '';
        }

        $allRoles = array();
        while ($row_role = mysql_fetch_assoc($result))
            $allRoles[] = $row_role['RoleID'];
        mysql_free_result($result);
        
        return implode(" ", $allRoles);
    }

    function ReadAllRoles()
    {
    	// Nuskaito visą rolių lentelę
        global  $loginDB;

        $query = "SELECT RoleId, Name FROM roles ORDER BY RoleId";
        $result = mysql_query($query, $loginDB);
        if (!$result)
        {
            return $array = array();
        }

        $allRoles = array();
        while ($row_role = mysql_fetch_assoc($result))
            $allRoles[] = $row_role;
        mysql_free_result($result);
        
        return $allRoles;
    }

    // Nuskaito vieną puslapį
    // Parametrai:
    //  $limit - užrašų skaičius puslapyhe
    //  $page - puslapio numeris
    //  $sidx - laukas pagal kurį reikia išrūšiuoti,
    //  $sord - rūšiavimo kryptis
    //  $searchField - laukas pagal kurį filtruojame
    //  $searchOper - palyginimo operacija
    //  Palyginima reikšmė
    function FetchAllUsers($limit, $page, $sidx, $sord, $searchField, $searchOper, $searchString)
    {
        global  $loginDB;
        $whereCond = GetWhereCondition($searchField, $searchOper, $searchString);

        $query = "SELECT COUNT(1) AS count FROM users {$whereCond}";
        $result = mysql_query($query, $loginDB);
        if (!$result)
            $count = 0;
        else
        {
            $row = mysql_fetch_assoc($result);
            $count = $row['count'];        
        }

        $total_pages = $count > 0 ? ceil($count / $limit) : 0;
        if ($page > $total_pages) 
            $page = $total_pages;
        $rzlt = new stdClass();
        $rzlt->page = $page;
        $rzlt->total = $total_pages;
        $rzlt->records = $count;
        $rzlt->rows = array();

        if ($count > 0)
        {
            $start = $limit * $page - $limit;
            $sortOrder = strcasecmp(trim($sord), 'ascending') == 0 ? 'ASC' : 'DESC';
            $SQL = "SELECT eMail, Name, IsActive, IsAdmin FROM users {$whereCond} ORDER BY {$sidx} {$sortOrder} LIMIT {$start}, {$limit}";
            $result = mysql_query( $SQL ) or die("SQL klaida.".mysql_error());
            while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $rzlt->rows[] = array('eMail' => $row['eMail'], 
                					  'Name' => $row['Name'], 
                					  'IsActive' => $row['IsActive'] == "1", 
                					  'IsAdmin' => $row['IsAdmin'] == "1");
            }
        }

        mysql_free_result($result);
        return $rzlt;
    }

    function GetWhereCondition($searchField, $searchOper, $searchString)
    {
        if (!$searchField || !$searchOper)  //Šie 2 laukai yra būtini paieškai
            return '';

        switch ($searchField)
        {
            case 'Name':
                return GetWhereForText($searchField, $searchOper, $searchString);
            case 'IsActive':
            case 'IsAdmin':
                return GetWhereForBoolean($searchField, $searchOper, $searchString);
            case 'allRoles':
                return GetWhereForRoles($searchOper, $searchString);
            default:
                return '';
        }
    }
    function GetWhereForRoles($searchOper, $searchString)
    {
        if ($searchOper == 'cn' || $searchOper == 'nc')
        {
            $not = $searchOper == 'nc' ? 'NOT' : '';
            $rzlt = "WHERE {$not} EXISTS ";
            $rzlt .= "(SELECT RoleID FROM userinrole WHERE userinrole.eMail = users.eMail AND userinrole.RoleID = '{$searchString}')";
            return $rzlt;
        }
        else
            return '';
    }

    function GetWhereForBoolean($searchField, $searchOper, $searchString)
    {
//todo: tikrinti;
        switch ($searchOper)
        {
            case 'eq':
                return "WHERE {$searchField} = '{$searchString}'";
            case 'ne':
                return "WHERE {$searchField} <> '{$searchString}'";
            default:
                return '';
        }
    }
    function GetWhereForText($searchField, $searchOper, $searchString)
    {
        switch ($searchOper)
        {
            case 'eq':
                return "WHERE {$searchField} = '{$searchString}'";
            case 'ne':
                return "WHERE {$searchField} <> '{$searchString}'";
            case 'bw':
                return "WHERE {$searchField} LIKE '{$searchString}%'";
            case 'bn':
                return "WHERE {$searchField} NOT LIKE '{$searchString}%'";
            case 'in':
                return "WHERE {$searchField} LIKE '%{$searchString}%'";
            case 'ni':
                return "WHERE {$searchField} NOT LIKE '%{$searchString}%'";
            case 'ew':
                return "WHERE {$searchField} LIKE '%{$searchString}'";
            case 'en':
                return "WHERE {$searchField} NOT LIKE '%{$searchString}'";
            default:
                return '';
        }
    }
    function UpdateUserData($newData)
    {
        global  $loginDB;

        $query = sprintf("SELECT COUNT(1) AS count FROM users u WHERE u.eMail = '%s'", $newData->eMail);
        $result = mysql_query($query, $loginDB);
        if (!$result)
            return "Nežinoma duomenų bazės klaida: nepavyko suskaičiuoti užrašų lentoje 'users'";
        else
        {
            $row = mysql_fetch_assoc($result);
            mysql_free_result($result);
            $count = $row['count'];

            if (strcasecmp($newData->operation, "insert") == 0)
                return InsertNewRow($newData, $count);
            else if (strcasecmp($newData->operation, "delete") == 0)
                return DeleteRow($newData, $count);
            else
                return UpdateRow($newData, $count);
        }
    }

    function DeleteRow($newData, $count)
    {
        global  $loginDB;

        if ($count < 1)
            return sprintf("%s duomenų bazėje nėra.", $newData->eMail);
        $errorMsg = UpdateRoles($newData, false);

        if (!$errorMsg)
        {
            $query = sprintf("DELETE FROM users WHERE eMail = '%s'", $newData->eMail);
            $errorMsg = mysql_query($query, $loginDB) ? '' : mysql_error($loginDB);
        }
        return $errorMsg;
    }

    function UpdateRow($newData, $count)
    {
        global  $loginDB;

        if ($count < 1)
            return sprintf("%s duomenų bazėje nėra.", $newData->eMail);
        $query = sprintf("UPDATE users SET Name = '%s', IsActive = '%s', IsAdmin = '%s' WHERE eMail = '%s'", 
                         $newData->Name, $newData->IsActive ? '1' : '0', $newData->IsAdmin ? '1' : '0', $newData->eMail);
        $errorMsg = mysql_query($query, $loginDB) ? '' : mysql_error($loginDB);
        if (!$errorMsg)
            $errorMsg = UpdateRoles($newData, true);
        return $errorMsg;
    }

    function InsertNewRow($newData, $count)
    {
        global  $loginDB;
        if ($count > 0)
            return sprintf("%s jau įrašytas duomenų bazėn.", $newData->eMail);
        $query = sprintf("INSERT INTO users VALUES ('%s', '%s', '%s', '%s')", 
                         $newData->eMail, $newData->Name, $newData->IsActive ? '1' : '0', $newData->IsAdmin ? '1' : '0');
        $errorMsg = mysql_query($query, $loginDB) ? '' : mysql_error($loginDB);
        if (!$errorMsg)
            $errorMsg = UpdateRoles($newData, true);
        return $errorMsg;
    }

    function UpdateRoles($newData, $insertNewRoles)
    {
        global  $loginDB;

        $query = sprintf("DELETE FROM userinrole WHERE eMail = '%s'", $newData->eMail);
        if (!mysql_query($query, $loginDB))
            return  mysql_error($loginDB);
        
        if ($insertNewRoles)
        {
            foreach (explode(' ', $newData->AllRoles) as $roleValue)    
            {
                $query = sprintf("INSERT INTO userinrole VALUES ('%s', '%s')", $newData->eMail, $roleValue);
                if (!mysql_query($query, $loginDB))
                    return  mysql_error($loginDB);
            }
        }
        return '';
    }

//--------------------------------------

    // Nuskaito vieną puslapį
    // Parametrai:
    //  $limit - užrašų skaičius puslapyhe
    //  $page - puslapio numeris
    //  $sidx - laukas pagal kurį reikia išrūšiuoti,
    //  $sord - rūšiavimo kryptis
    //  $searchField - laukas pagal kurį filtruojame
    //  $searchOper - palyginimo operacija
    //  Palyginima reikšmė
    function FetchAllTasks($tblName, $limit, $page, $sidx, $sord, $searchField, $searchOper, $searchString)
    {
        global  $workDB;
/*
$myFile = "C:\\Gedimino\\temp2\\testFile.txt";
$fh = fopen($myFile, 'w') or die("can't open file");		
$stringData = "00 tblName={$tblName} limit={$limit} page={$page} sidx={$sidx} searchField={$searchField} searchOper={$searchOper} searchString={$searchString}\n";
fwrite($fh, $stringData);
*/
        if (!isset($_SESSION)) {
			session_start();
		}
        $joinCond = "{$tblName}.lentele = tableindexes.id";
        $whereCond = CreateWhereForSubtable($tblName, $searchField, $searchOper, $searchString);

		if ($tblName == "uzduotis_create") {
            $query = "SELECT COUNT(1) AS count FROM {$tblName} {$whereCond}";            
        } else {
            $query = "SELECT COUNT(1) AS count FROM {$tblName} INNER JOIN tableindexes ON {$joinCond} {$whereCond}";
		}

/*
$stringData = "01 {$query}\n";
fwrite($fh, $stringData);
*/
        $result = mysql_query($query, $workDB);
        if (!$result)
            $count = 0;
        else
        {
            $row = mysql_fetch_assoc($result);
            $count = $row['count'];        
        }
        $total_pages = $count > 0 ? ceil($count / $limit) : 0;
        if ($page > $total_pages) 
            $page = $total_pages;
/*
$stringData = "02 total_pages={$total_pages} page={$page}\n";
fwrite($fh, $stringData);
*/
        $rzlt = new stdClass();
        $rzlt->page = $page;
        $rzlt->total = $total_pages;
        $rzlt->records = $count;
        $rzlt->rows = array();
/*
$stringData = "02 page={$rzlt->page}\n total={$rzlt->total}\n records=$rzlt->records\n";
fwrite($fh, $stringData);
*/
        if ($count > 0)
        {
            $start = $limit * $page - $limit;
			if ($tblName != "uzduotis_create") {
            	$SQL = "SELECT {$tblName}.id, {$tblName}.salyga, {$tblName}.komanda, tableindexes.pavadinimas FROM {$tblName} 
            			INNER JOIN tableindexes ON {$joinCond} {$whereCond} ORDER BY {$sidx} {$sord} LIMIT {$start}, {$limit}";
			}
			else {
            	$SQL = "SELECT * FROM {$tblName} {$whereCond} ORDER BY {$sidx} {$sord} LIMIT {$start}, {$limit}";			
			}
/*
$stringData = "03 SQL={$SQL}\n";
fwrite($fh, $stringData);
*/
            $result = mysql_query( $SQL, $workDB ) or die("SQL klaida.".mysql_error());
            while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            	if ($tblName != "uzduotis_create") {
	                $rzlt->rows[] = array('id' => $row['id'],
	                					  'task' => $row['salyga'], 
	                					  'tbl' => $row['pavadinimas'], 
	                					  'command' => $row['komanda']); 	
				}
				else {
	                $rzlt->rows[] = array('id' => $row['id'],
	                					  'task' => $row['salyga'], 
	                					  'tbl' => '', 
	                					  'command' => ''); 
				}
            }
/*
foreach ($rzlt->rows as $key => $value)
{
    $stringData = "03 {$key} id={$value['id']} task={$value['task']} tbl={$value['tbl']} command={$value['command']}\n";
    fwrite($fh, $stringData);
}
fclose($fh);
*/
        }
		else {
			$rzlt->rows[] = array();
		}
        mysql_free_result($result);
        return $rzlt;
    }

    function CreateWhereForSubtable($tblName, $searchField, $searchOper, $searchString)
    {
        if (!$tblName || !$searchField || !$searchOper || !$searchString)   // Būtini visi laukai
            return '';
        switch ($searchOper)
        {
            case 'eq':
                return "WHERE {$tblName}.{$searchField} = '{$searchString}'";
            case 'ne':
                return "WHERE {$tblName}.{$searchField} <> '{$searchString}'";
            case 'bw':
                return "WHERE {$tblName}.{$searchField} LIKE '{$searchString}%'";
            case 'bn':
                return "WHERE {$tblName}.{$searchField} NOT LIKE '{$searchString}%'";
            case 'in':
                return "WHERE {$tblName}.{$searchField} LIKE '%{$searchString}%'";
            case 'ni':
                return "WHERE {$tblName}.{$searchField} NOT LIKE '%{$searchString}%'";
            case 'ew':
                return "WHERE {$tblName}.{$searchField} LIKE '%{$searchString}'";
            case 'en':
                return "WHERE {$tblName}.{$searchField} NOT LIKE '%{$searchString}'";
            default:
                return '';
        }
    }

	function UpdateTasksData($newData)
	{
        global  $workDB;
        $query = sprintf("SELECT COUNT(1) AS count FROM temupavadinimai u WHERE u.id = '%d'", $newData->id);
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.xml";
$fh = fopen($myFile, 'w') or die("can't open file");        
$stringData = "query={$query}\n";
fwrite($fh, $stringData);
$stringData = "id={$newData->id}\n";
fwrite($fh, $stringData);
fclose($fh);
*/
        $result = mysql_query($query, $workDB);
        if (!$result) {
            return "Nežinoma duomenų bazės klaida: nepavyko suskaičiuoti užrašų lentoje 'temupavadinimai'";
        }
        else
        {
            $row = mysql_fetch_assoc($result);
            mysql_free_result($result);
            $count = $row['count'];
            return UpdateTasksRow($newData, $count);
        }	
	}


    function UpdateTasksRow($newData, $count)
    {
        global  $workDB;

        if ($count < 1)
            return sprintf("'%s' duomenų bazėje nėra.", $newData->tema);
        $query = sprintf("UPDATE temupavadinimai SET tema = '%s' WHERE id = '%d'", $newData->tema, $newData->id);
/*
$myFile = "C:\\Gedimino\\temp2\\testFile5.xml";
$fh = fopen($myFile, 'w') or die("can't open file");        
$stringData = "query={$query}\n";
fwrite($fh, $stringData);
fclose($fh);
*/
        $errorMsg = mysql_query($query, $workDB) ? '' : mysql_error($workDB);

        return $errorMsg;
    }

	function GetAllTables()
	{
		global  $workDB;
//        global $conf;
        $tables = array();


//		$sql = "SHOW TABLES FROM {$conf['db_name2']}";
        $sql = "SELECT * FROM tableindexes";
		$result = mysql_query($sql, $workDB);
		if (!$result) {
		    echo 'MySQL Error: ' . mysql_error();
		    return $tables;
		}
/*
$myFile = "C:\\Gedimino\\temp2\\testFile6.xml";
$fh = fopen($myFile, 'w') or die("can't open file"); 
*/
		while ($row = mysql_fetch_assoc($result)) {
            $i = $row['id'];
            $tables[$i] = $row;
		    if($i == 1) {
                $tables[$i] = '[ Vartotojo sukurta lentelė ]';                            
            } else {
                $tables[$i] = $row['pavadinimas'];
            }
/*$stringData = "i={$i} tables[i]={$tables[$i]}\n";
fwrite($fh, $stringData);*/
		}
		mysql_free_result($result);
/*
fclose($fh);
*/
		return $tables;
	}


    function GetTaskTableNames()
    {
        global $workDB;
        global $conf;
        $sql = "SHOW TABLES FROM  {$conf['db_name1']} LIKE 'uzduotis_%'";
        $tables = array();

        $result = mysql_query($sql, $workDB);
        if (!$result) {
            echo 'MySQL Error: ' . mysql_error();
            return tables;
        }
        $i = 1;
        while ($row = mysql_fetch_array($result)) {
            $tables[$i++] = $row[0];
        }
        mysql_free_result($result);

        return $tables;
    }

    function InsertUzduotisCreate($data)
    {
        global $workDB;
        $sql = "INSERT INTO uzduotis_create (salyga) VALUES ('{$data->taskDescription}')";
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return mysql_insert_id();
    }
    function InsertKitosLentos($data, $tableName)
    {
        global $workDB;
        $sql = "INSERT INTO {$tableName} (salyga, Lentele, Komanda) VALUES ".
            "('{$data->taskDescription}', {$data->selectedTable}, '{$data->sqlDefinition}')";
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $sql);
fclose($fh);
*/
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return mysql_insert_id();
    }
    function UpdateUzduotisCreate($data)
    {
        global $workDB;
        $sql = "UPDATE uzduotis_create SET salyga='{$data->taskDescription}' WHERE id={$data->currentRecordId}";
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $sql);
fclose($fh);
*/
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return 0;
    }
    function UpdateKitosLentos($data, $tableName)
    {
        global $workDB;
        $sql = "UPDATE {$tableName} SET salyga='{$data->taskDescription}', Lentele={$data->selectedTable}, ".
                    "Komanda='{$data->sqlDefinition}' WHERE id={$data->currentRecordId}";
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $sql);
fclose($fh);
*/
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return 0;
    }
    function DeleteUzduotisCreate($data)
    {
        global $workDB;
        $sql = "DELETE FROM uzduotis_create WHERE id={$data->currentRecordId}";
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $sql);
fclose($fh);
*/
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return 0;
    }
    function DeleteKitosLentos($data, $tableName)
    {
        global $workDB;
        $sql = "DELETE FROM {$tableName} WHERE id={$data->currentRecordId}";
/*
$myFile = "C:\\Gedimino\\temp2\\testFile4.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $sql);
fclose($fh);
*/
        if (!mysql_query($sql, $workDB))
            return  -1;
        else
            return 0;
    }
?>